<?php
class ModelMartCrmgoodsgroup3 extends Model {
	public function getGoodsGroup() { 
		$sql = "SELECT crm_industry.id, crm_industry.name, SUM( crm_customer_transactions.amount ) AS amount
                        FROM crm_goods, crm_customer_transactions, crm_customer_transactions_crm_goods_c, crm_industry, crm_goods_crm_industry_c
                        WHERE crm_industry.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_industry_ida
                        AND crm_goods.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_goods_idb
                        AND (
                        crm_goods.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_goods_ida
                        )
                        AND (
                        crm_customer_transactions.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_customer_transactions_idb
                        )
                        GROUP BY crm_industry.name
                        ORDER BY SUM( crm_customer_transactions.amount ) DESC ";
		//$sql = "select * from accounts";	
                
		$query = $this->db->query($sql);
		return $query->rows;
                echo $query;
	}
        
        public function getGoodsGroup3($data = array())
        {
                $sql = "SELECT crm_industry.id as id_group_3, crm_industry.name as name, SUM( crm_customer_transactions.amount ) AS amount
                FROM crm_goods, crm_customer_transactions, crm_customer_transactions_crm_goods_c, crm_industry, 
                crm_goods_crm_industry_c, crm_industry_crm_sec_industry_c, crm_customer_bills, crm_customer_transactions_crm_customer_bills_c
                WHERE crm_industry.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_industry_ida
                AND crm_goods.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_goods_idb
                AND (
                crm_goods.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_goods_ida
                )
                AND (
                crm_customer_transactions.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_customer_transactions_idb
                )
                AND crm_customer_transactions.id = crm_customer_transactions_crm_customer_bills_c.crm_customaf9eactions_idb
                AND crm_customer_bills.id = crm_customer_transactions_crm_customer_bills_c.crm_custom19b9r_bills_ida
                and crm_industry.id = crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_industry_idb ";
            if (!empty($data['id_group_2'])) {
                $sql .= "and crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_sec_industry_ida ='". $this->db->escape($data['id_group_2'])."'";
            }
            if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(crm_customer_bills.bill_date) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(crm_customer_bills.bill_date) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
                $sql .= " GROUP BY crm_industry.name
                ORDER BY SUM( crm_customer_transactions.amount ) DESC 
                ";
              //echo $sql;
                $query = $this->db->query($sql);
		return $query->rows;
               
        }

	public function getTotalCustomersOnline($data = array()) {
		$sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "customer_online` co LEFT JOIN " . DB_PREFIX . "customer c ON (co.customer_id = c.customer_id)";
		
		$implode = array();
		
		if (isset($data['filter_ip']) && !is_null($data['filter_ip'])) {
			$implode[] = "co.ip LIKE '" . $this->db->escape($data['filter_ip']) . "'";
		}
		
		if (isset($data['filter_customer']) && !is_null($data['filter_customer'])) {
			$implode[] = "co.customer_id > 0 AND CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "'";
		}
		
		if ($implode) {
			$sql .= " WHERE " . implode(" AND ", $implode);
		}
				
		$query = $this->db->query($sql);

		return $query->row['total'];
	}
}
?>